package db.document;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.Database;
import db.history.SystemLogDB;
import db.history.经营情况DB;
import po.AccountBookPo;
import po.DocumentPo;
import po.Po;
import po.SystemLogPo;
import po.库存报损报溢单po;

public class StockScrapAndOverflowDocumentDB extends DocumentDB{

	
	public StockScrapAndOverflowDocumentDB(AccountBookPo ab) {
		super(ab);
		String createT_lessDoc="CREATE TABLE IF NOT EXISTS `seac2`.`"+book.getName()+ "_Doc_less_more` ( `f_id` varchar(40) NOT NULL, `f_commodityID` varchar(20) NOT NULL, `f_commodityName` varchar(20) NOT NULL, `f_commodityModel` varchar(20) NOT NULL, `f_num` int(8) NOT NULL,`f_date` date NOT NULL, `f_isApproved` int(1) NOT NULL  DEFAULT  '0', `f_remark` varchar(50) , `f_type` int(1) NOT NULL  DEFAULT  '0', PRIMARY KEY (`f_id`),UNIQUE KEY `f_id` (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.execute(createT_lessDoc);
			s.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public Po add(DocumentPo po) {
		Po m=new Po();int total_num=0;
		Connection c = Database.getConnection();
		String id=null;int stock=0;
		String commodityID=((库存报损报溢单po) po).get商品编号();
		String commodityName=((库存报损报溢单po) po).get商品名称();
		String commodityModel=((库存报损报溢单po) po).get商品型号();
		String remark=((库存报损报溢单po) po).get备注();
		int num=((库存报损报溢单po) po).getNum();
		int type=((库存报损报溢单po) po).isType()==true? 1:0;          //为1则为报损单，为0则为报溢单
		String date=new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		try {
			Statement s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_Doc_less_more");
			while(rs.next()){
				total_num++;
			}
			if(type==1){
		        id=getDocumentId("KCBSD",total_num);
		        ResultSet rs1=s.executeQuery("select f_name,f_model,f_stockNum from "+book.getName()+"_commodity where f_commodityID='"+commodityID+"'");
			    while(rs1.next()){
			    	commodityName=rs1.getString(1);
			    	commodityModel=rs1.getString(2);
			    	stock=rs1.getInt(3);
			    }
			    if(stock<num){
			    	m.failed("您填写的报损数量大于库存数量！这会使库存变为负值！");
			    	return m;
			    }
			}else{
				id=getDocumentId("KCBYD",total_num);
			}
			s.execute("insert into "+book.getName()+"_Doc_less_more(f_id,f_commodityID,f_commodityName,f_commodityModel,f_num,f_date,f_remark,f_type) values('"+id+"','"+commodityID+"','"+commodityName+"','"+commodityModel+"','"+num+"','"+date+"','"+remark+"','"+type+"')");
			m.successed();
			s.close();
			SystemLogDB log=new SystemLogDB();
			SystemLogDB.iniSystemLogDB();
			log.add(new SystemLogPo(po.get操作员(),"add",id));
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public Po update(DocumentPo po) {
		String doc_id=((库存报损报溢单po) po).get单据编号();
		Po m=new Po();
		String commodityID=((库存报损报溢单po) po).get商品编号();
		String commodityName=((库存报损报溢单po) po).get商品名称();
		String commodityModel=((库存报损报溢单po) po).get商品型号();
		String remark=((库存报损报溢单po) po).get备注();
		int num=((库存报损报溢单po) po).getNum();
		Connection c = Database.getConnection();
		Statement s;
		try {
			 s = c.createStatement();
			 s.execute("update "+book.getName()+"_Doc_less_more set f_commodityID='"+commodityID+"',f_commodityName='"+commodityName+"',f_commodityModel='"+commodityModel+"',f_num='"+num+"',f_remark='"+remark+"' where f_id='"+doc_id+"'");
			m.successed();
			s.close();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public List<DocumentPo> getUnapproval() {
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		Connection c=Database.getConnection();
		Statement s;ResultSet rs = null;
		try {
			s = c.createStatement();
			rs=s.executeQuery("select * from "+book.getName()+"_Doc_less_more where f_isApproved='"+0+"'");
			while(rs.next()){
				库存报损报溢单po po=new 库存报损报溢单po();
				po.set单据编号(rs.getString(1));
				po.set商品编号(rs.getString(2));
				po.set商品名称(rs.getString(3));
				po.set商品型号(rs.getString(4));
				po.setNum(rs.getInt(5));
				po.set备注(rs.getString("f_remark"));
				po.setType(rs.getInt("f_type")==1?true:false);
				list.add(po);
		   }
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;	

	}

	@Override
	public List<DocumentPo> get(DocumentPo from, DocumentPo to) {
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		String from_date;String to_date;int type=from.isType()? 1:0;//报损单==1
		from_date=from.getDate()==null? null:new SimpleDateFormat("yyyy-MM-dd").format(from.getDate());
		to_date=to.getDate()==null? null:new SimpleDateFormat("yyyy-MM-dd").format(to.getDate());
		String query_from=from_date==null? "":"and f_date>='"+from_date+"'";
		String query_to=to_date==null? "":"and f_date<='"+to_date+"'";
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_Doc_less_more where f_isApproved='"+1+"'"+query_from+query_to+"and f_type='"+type+"'");
			while(rs.next()){
				库存报损报溢单po po=new 库存报损报溢单po();
				po.set单据编号(rs.getString(1));
				po.set商品编号(rs.getString(2));
				po.set商品名称(rs.getString(3));
				po.set商品型号(rs.getString(4));
				po.setNum(rs.getInt(5));
				po.set备注(rs.getString("f_remark"));
				list.add(po);
		   }
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;	}

	@Override
	
	public Po approve(DocumentPo po) {
		String docid=((库存报损报溢单po)po).get单据编号();
		String id=((库存报损报溢单po)po).get商品编号();
		Po m=new Po();int num=0;int type=0;long av=0;//库存均价
		Connection c=Database.getConnection();
		Statement s;
		try {
			
			s = c.createStatement();
			ResultSet rs5=s.executeQuery("select f_isApproved from "+book.getName()+"_Doc_less_more where f_id='"+docid+"'");
			while(rs5.next()){
				if(rs5.getInt(1)==1){
					m.failed("该单据已被审批！");
					return m;
				}
			}

		    经营情况DB db=new 经营情况DB(book);
			
			s.executeUpdate("update "+book.getName()+"_Doc_less_more set f_isApproved='"+1+"' where f_id='"+docid+"'");
			
			
			ResultSet rs=s.executeQuery("select f_num,f_type from "+book.getName()+"_Doc_less_more where f_id='"+docid+"'");
			while(rs.next()){
				num=rs.getInt(1);
				type=rs.getInt("f_type");
			}
			ResultSet rs1=s.executeQuery("select f_stockAveragePrice from "+book.getName()+"_commodity where f_commodityID='"+id+"'");
			while(rs1.next()){
				av=rs1.getLong(1);
			}
			 //库存报溢单审批后，add相应的库存数量
			if(type==0){
			    s.execute("update "+book.getName()+"_commodity set f_stockNum=(f_stockNum+"+num+") where f_commodityID='"+id+"'");
			    s.execute("update "+book.getName()+"_commodity set f_totalMoney=(f_totalMoney+"+num*av+") where f_commodityID='"+id+"'");
			    s.execute("update "+book.getName()+"_commodity set f_stockAveragePrice=(case when f_stockNum=0 then 0 else f_totalMoney/f_stockNum end)"+" where f_commodityID='"+id+"'");
			   
			    //商品报溢收入,调用经营情况
			    Po r1=db.add商品报溢收入(num*av);
			    if(!r1.isSuccessed()){
			    	m.failed("添加商品报溢收入失败！");
			    	return m;
			    }
			    
			    
			}else if(type==1){
				s.execute("update "+book.getName()+"_commodity set f_stockNum=(f_stockNum-"+num+") where f_commodityID='"+id+"'");
				 s.execute("update "+book.getName()+"_commodity set f_totalMoney=(f_totalMoney-"+num*av+") where f_commodityID='"+id+"'");
				 s.execute("update "+book.getName()+"_commodity set f_stockAveragePrice=(case when f_stockNum=0 then 0 else f_totalMoney/f_stockNum end)"+" where f_commodityID='"+id+"'");
				 //商品报损支出
				 Po r2=db.add商品报损支出(num*av);
				 if(!r2.isSuccessed()){
					 m.failed("添加商品报损支出失败！");
				    return m;
				 }
				 
			}
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}
	

}
